import os, sys
import numpy as np
import pandas as pd
import matplotlib.pyplot as plt
from iotdb.Session import Session
from iotdb.template.MeasurementNode import MeasurementNode
from iotdb.template.Template import Template
from iotdb.utils.BitMap import BitMap
from iotdb.utils.IoTDBConstants import TSDataType, TSEncoding, Compressor
from iotdb.utils.Tablet import Tablet
from iotdb.utils.NumpyTablet import NumpyTablet
import datetime
import json

# -----------function to load dataframe into iotdb--------------
def loadMeterDatafromDataFrame(writedf_meter):
    ntimestamps = writedf_meter.shape[0]
    timelst = []
    # note, very important!!!!!!!!! The timestamp should be the standard one *by 1000!
    for idx in range(ntimestamps):
        timeArray = datetime.datetime.strptime(writedf_meter.iloc[idx, 0], "%Y-%m-%d %H:%M")
        timeStamp = int(datetime.datetime.timestamp(timeArray)*1000)
        timelst.append(timeStamp)
    np_timestamps_ = np.array(timelst, TSDataType.INT64.np_dtype())

    for idx in range(1, len(writedf_meter.columns)):
        np_values_ = [writedf_meter.iloc[:, idx].values]
        strlsttmp = writedf_meter.columns[idx].split('.')
        nlen = len(strlsttmp[-1])
        devpath_ = writedf_meter.columns[idx][:-(nlen + 1)]
        measurements_ = [strlsttmp[-1]]
        data_types_ = [
            TSDataType.FLOAT,
        ]
        np_tablet_ = NumpyTablet(
            devpath_, measurements_, data_types_, np_values_, np_timestamps_
        )
        session.insert_tablet(np_tablet_)

    print('finished data loading from dateframe finished')

# -----------function to load csvfile into iotdb--------------
def loadMeterDatafromCsvFile(csvfile):
    writedf_meter = pd.read_csv(csvfile)
    ntimestamps = writedf_meter.shape[0]
    timelst = []
    #note, very important!!!!!!!!! The timestamp should be the standard one *by 1000!
    for idx in range(ntimestamps):
        timeArray = datetime.datetime.strptime(writedf_meter.iloc[idx, 0], "%Y-%m-%d %H:%M:%S")
        timeStamp = int(datetime.datetime.timestamp(timeArray)*1000)
        timelst.append(timeStamp)
    np_timestamps_ = np.array(timelst, TSDataType.INT64.np_dtype())

    for idx in range(1, len(writedf_meter.columns)):
        np_values_ = [writedf_meter.iloc[:, idx].values]
        strlsttmp = writedf_meter.columns[idx].split('.')
        nlen = len(strlsttmp[-1])
        devpath_ = writedf_meter.columns[idx][:-(nlen + 1)]
        measurements_ = [strlsttmp[-1]]
        data_types_ = [
            TSDataType.FLOAT,
        ]
        np_tablet_ = NumpyTablet(
            devpath_, measurements_, data_types_, np_values_, np_timestamps_
        )
        session.create_time_series(
            writedf_meter.columns[idx], TSDataType.FLOAT, TSEncoding.PLAIN, Compressor.SNAPPY
        )
        session.insert_tablet(np_tablet_)

    print('------------finished data loading into iotdb from csv file %s ------------\n'%(csvfile))

# connect to the iotdb
ip = "127.0.0.1"
port_ = "6667"
username_ = "root"
password_ = "root"
session = Session(ip, port_, username_, password_)
session.open(False)

HEADGATEWAYID = 'RKSimuvppplt01'

# check whether the specific data storage 'root.RKSimuvppplt01' already exists or not, if yes, delete it
qureystr = 'SHOW STORAGE GROUP'
res = session.execute_query_statement(qureystr)
dfgp = res.todf()
for idx, irow in dfgp.iterrows():
    if irow[0] == 'root.%s'%(HEADGATEWAYID):
        print(
            'The specific data storage \'root.RKSimuVpp01\' already exist, the program will first delete it and reload it \n')
        session.delete_storage_group('root.%s'%(HEADGATEWAYID))

# create the specific data storage 'root.Simuvppplt01'
session.set_storage_group('root.%s'%(HEADGATEWAYID))

csvfile = 'IniPower.csv'
readdf = pd.read_csv(csvfile)

headnames = []
headnames.append('Time')

column_names = readdf.columns[1:].tolist()
nameDict = {}
startDevid = 20020000000
for idx, ele in enumerate(column_names):
    strtmp = ele.split('_')
    strtmp1 = strtmp[0].split(' ')
    if len(strtmp) >= 4:
        areaStr = '%s_%03d'%(strtmp1[0],int(strtmp1[1]))
        devidStr = '%s_%04d_%d@%s'%(strtmp[1], int(strtmp[2]), startDevid, areaStr)
        startDevid += 1
        strName = 'root.%s.RK99900000003.`%s`.`%s`.P_ini'%(HEADGATEWAYID, areaStr, devidStr)
        nameDict[ele] = strName
        headnames.append(strName)
    else:
        areaStr = '%s_%03d' % (strtmp1[0], int(strtmp1[1]))
        devidStr = '%s_%d@%s' % (strtmp[1], startDevid, areaStr)
        startDevid += 1
        if strtmp[1] == 'Temperature':
            strName = 'root.%s.RK99900000003.`%s`.`%s`.T_ini' % (HEADGATEWAYID, areaStr, devidStr)
        else:
            strName = 'root.%s.RK99900000003.`%s`.`%s`.P_ini' % (HEADGATEWAYID, areaStr, devidStr)
        nameDict[ele] = strName
        headnames.append(strName)

writedf = readdf.copy()
writedf.columns = headnames

csvfileLD = 'LDPower2.csv'
lddf = pd.read_csv(csvfileLD)

#------------add the columns for the zone1 to zone3, and total  EV, AC, LD------------------------
#-----------zone 1--------------------------------------------------------------------------------
areaStr = 'Area_001'
datatype = 'P_ini'
#devidStr = '%s_%d@%s' % (strtmp[1], startDevid, areaStr)

# zone 1 ac, column idx 1060
colName = 'root.%s.RK99900000003.`%s`.`AC_total@%s`.%s'%(HEADGATEWAYID, areaStr, areaStr, datatype)
writedf[colName] = writedf.iloc[:, 1:300].sum(axis=1)

# zone 1 ev, column idx 1061
colName = 'root.%s.RK99900000003.`%s`.`EV_total@%s`.%s'%(HEADGATEWAYID, areaStr, areaStr, datatype)
writedf[colName] = writedf.iloc[:, 301:350].sum(axis=1)

# zone 1 total load, mode 1, column idx 1062
colName = 'root.%s.RK99900000003.`%s`.`LD1_total@%s`.%s'%(HEADGATEWAYID, areaStr, areaStr, datatype)
writedf[colName] = writedf.iloc[:, 1:350].sum(axis=1) + writedf.iloc[:, 352] - writedf.iloc[:, 351]

# zone 1 total load, mode 2, column idx 1063
colName = 'root.%s.RK99900000003.`%s`.`LD2_total@%s`.%s'%(HEADGATEWAYID, areaStr, areaStr, datatype)
writedf[colName] = writedf.iloc[:, 1:350].sum(axis=1) + lddf.iloc[:, 1] - writedf.iloc[:, 351]

# zone 1 total load, mode 3, column idx 1064
colName = 'root.%s.RK99900000003.`%s`.`LD3_total@%s`.%s'%(HEADGATEWAYID, areaStr, areaStr, datatype)
writedf[colName] = writedf.iloc[:, 1:350].sum(axis=1) + lddf.iloc[:, 2] - writedf.iloc[:, 351]

#-----------zone 2--------------------------------------------------------------------------------
areaStr = 'Area_002'
datatype = 'P_ini'
#devidStr = '%s_%d@%s' % (strtmp[1], startDevid, areaStr)
# zone 2 ac, column idx 1065
colName = 'root.%s.RK99900000003.`%s`.`AC_total@%s`.%s'%(HEADGATEWAYID, areaStr, areaStr, datatype)
writedf[colName] = writedf.iloc[:, 1+353:300+353].sum(axis=1)

# zone 2 ev, column idx 1066
colName = 'root.%s.RK99900000003.`%s`.`EV_total@%s`.%s'%(HEADGATEWAYID, areaStr, areaStr, datatype)
writedf[colName] = writedf.iloc[:, 301+353:350+353].sum(axis=1)

# zone 2 total load, mode 1, column idx 1067
colName = 'root.%s.RK99900000003.`%s`.`LD1_total@%s`.%s'%(HEADGATEWAYID, areaStr, areaStr, datatype)
writedf[colName] = writedf.iloc[:, 1+353:350+353].sum(axis=1) + writedf.iloc[:, 352+353] - writedf.iloc[:, 351+353]

# zone 2 total load, mode 2, column idx 1068
colName = 'root.%s.RK99900000003.`%s`.`LD2_total@%s`.%s'%(HEADGATEWAYID, areaStr, areaStr, datatype)
writedf[colName] = writedf.iloc[:, 1+353:350+353].sum(axis=1) + lddf.iloc[:, 1] - writedf.iloc[:, 351+353]

# zone 2 total load, mode 3, column idx 1069
colName = 'root.%s.RK99900000003.`%s`.`LD3_total@%s`.%s'%(HEADGATEWAYID, areaStr, areaStr, datatype)
writedf[colName] = writedf.iloc[:, 1+353:350+353].sum(axis=1) + lddf.iloc[:, 2] - writedf.iloc[:, 351+353]

#-----------zone 3--------------------------------------------------------------------------------
areaStr = 'Area_003'
datatype = 'P_ini'
#devidStr = '%s_%d@%s' % (strtmp[1], startDevid, areaStr)
# zone 3 ac, column idx 1070
colName = 'root.%s.RK99900000003.`%s`.`AC_total@%s`.%s'%(HEADGATEWAYID, areaStr, areaStr, datatype)
writedf[colName] = writedf.iloc[:, 1+353*2:300+353*2].sum(axis=1)

# zone 3 ev, column idx 1071
colName = 'root.%s.RK99900000003.`%s`.`EV_total@%s`.%s'%(HEADGATEWAYID, areaStr, areaStr, datatype)
writedf[colName] = writedf.iloc[:, 301+353*2:350+353*2].sum(axis=1)

# zone 3 total load, mode 1, column idx 1072
colName = 'root.%s.RK99900000003.`%s`.`LD1_total@%s`.%s'%(HEADGATEWAYID, areaStr, areaStr, datatype)
writedf[colName] = writedf.iloc[:, 1+353*2:350+353*2].sum(axis=1) + writedf.iloc[:, 352+353*2] - writedf.iloc[:, 351+353*2]

# zone 3 total load, mode 2, column idx 1073
colName = 'root.%s.RK99900000003.`%s`.`LD2_total@%s`.%s'%(HEADGATEWAYID, areaStr, areaStr, datatype)
writedf[colName] = writedf.iloc[:, 1+353*2:350+353*2].sum(axis=1) + lddf.iloc[:, 1] - writedf.iloc[:, 351+353*2]

# zone 3 total load, mode 3, column idx 1074
colName = 'root.%s.RK99900000003.`%s`.`LD3_total@%s`.%s'%(HEADGATEWAYID, areaStr, areaStr, datatype)
writedf[colName] = writedf.iloc[:, 1+353*2:350+353*2].sum(axis=1) + lddf.iloc[:, 2] - writedf.iloc[:, 351+353*2]

#-----------total--------------------------------------------------------------------------------
areaStr = 'Area_total'
datatype = 'P_ini'
#devidStr = '%s_%d@%s' % (strtmp[1], startDevid, areaStr)
# total ac, column idx 1075
colName = 'root.%s.RK99900000003.`%s`.`AC_total@%s`.%s'%(HEADGATEWAYID, areaStr, areaStr, datatype)
writedf[colName] = writedf.iloc[:, 1060] + writedf.iloc[:, 1060+5] + writedf.iloc[:, 1060+5*2]

# total ev, column idx 1076
colName = 'root.%s.RK99900000003.`%s`.`EV_total@%s`.%s'%(HEADGATEWAYID, areaStr, areaStr, datatype)
writedf[colName] = writedf.iloc[:, 1061] + writedf.iloc[:, 1061+5] + writedf.iloc[:, 1061+5*2]

# total load, mode 1, column idx 1077
colName = 'root.%s.RK99900000003.`%s`.`LD1_total@%s`.%s'%(HEADGATEWAYID, areaStr, areaStr, datatype)
writedf[colName] = writedf.iloc[:, 1062] + writedf.iloc[:, 1062+5] + writedf.iloc[:, 1062+5*2]

# total load, mode 2, column idx 1078
colName = 'root.%s.RK99900000003.`%s`.`LD2_total@%s`.%s'%(HEADGATEWAYID, areaStr, areaStr, datatype)
writedf[colName] = writedf.iloc[:, 1063] + writedf.iloc[:, 1063+5] + writedf.iloc[:, 1063+5*2]

# total load, mode 3, column idx 1078
colName = 'root.%s.RK99900000003.`%s`.`LD3_total@%s`.%s'%(HEADGATEWAYID, areaStr, areaStr, datatype)
writedf[colName] = writedf.iloc[:, 1064] + writedf.iloc[:, 1064+5] + writedf.iloc[:, 1064+5*2]

#-------------------------add opt 4, zone 1,2,3 total data for LD1, LD2, LD3-----------------------

datatype = 'P_opt4'

#------------- SCE 1-----------------------------
csvfileTmp = 'Se1_opt4.csv'
writedftmp = pd.read_csv(csvfileTmp)

# zone 1 total load, SCE 1,
areaStr = 'Area_001'
colName = 'root.%s.RK99900000003.`%s`.`LD1_total@%s`.%s'%(HEADGATEWAYID, areaStr, areaStr, datatype)
writedf[colName] = writedftmp.iloc[:, 1]

# zone 2 total load, SCE 1,
areaStr = 'Area_002'
colName = 'root.%s.RK99900000003.`%s`.`LD1_total@%s`.%s'%(HEADGATEWAYID, areaStr, areaStr, datatype)
writedf[colName] = writedftmp.iloc[:, 2]

# zone 3 total load, SCE 1,
areaStr = 'Area_003'
colName = 'root.%s.RK99900000003.`%s`.`LD1_total@%s`.%s'%(HEADGATEWAYID, areaStr, areaStr, datatype)
writedf[colName] = writedftmp.iloc[:, 3]

# total load, SCE 1,
areaStr = 'Area_total'
colName = 'root.%s.RK99900000003.`%s`.`LD1_total@%s`.%s'%(HEADGATEWAYID, areaStr, areaStr, datatype)
writedf[colName] = writedftmp.iloc[:, 4]

#------------- SCE 2-----------------------------
csvfileTmp = 'Se2_opt4.csv'
writedftmp = pd.read_csv(csvfileTmp)

# zone 1 total load, SCE 2,
areaStr = 'Area_001'
colName = 'root.%s.RK99900000003.`%s`.`LD2_total@%s`.%s'%(HEADGATEWAYID, areaStr, areaStr, datatype)
writedf[colName] = writedftmp.iloc[:, 1]

# zone 2 total load, SCE 2
areaStr = 'Area_002'
colName = 'root.%s.RK99900000003.`%s`.`LD2_total@%s`.%s'%(HEADGATEWAYID, areaStr, areaStr, datatype)
writedf[colName] = writedftmp.iloc[:, 2]

# zone 3 total load, SCE 2,
areaStr = 'Area_003'
colName = 'root.%s.RK99900000003.`%s`.`LD2_total@%s`.%s'%(HEADGATEWAYID, areaStr, areaStr, datatype)
writedf[colName] = writedftmp.iloc[:, 3]

# total load, SCE 2,
areaStr = 'Area_total'
colName = 'root.%s.RK99900000003.`%s`.`LD2_total@%s`.%s'%(HEADGATEWAYID, areaStr, areaStr, datatype)
writedf[colName] = writedftmp.iloc[:, 4]

#------------- SCE 3-----------------------------
csvfileTmp = 'Se3_opt4.csv'
writedftmp = pd.read_csv(csvfileTmp)

# zone 1 total load, SCE 2,
areaStr = 'Area_001'
colName = 'root.%s.RK99900000003.`%s`.`LD3_total@%s`.%s'%(HEADGATEWAYID, areaStr, areaStr, datatype)
writedf[colName] = writedftmp.iloc[:, 1]

# zone 2 total load, SCE 2
areaStr = 'Area_002'
colName = 'root.%s.RK99900000003.`%s`.`LD3_total@%s`.%s'%(HEADGATEWAYID, areaStr, areaStr, datatype)
writedf[colName] = writedftmp.iloc[:, 2]

# zone 3 total load, SCE 2,
areaStr = 'Area_003'
colName = 'root.%s.RK99900000003.`%s`.`LD3_total@%s`.%s'%(HEADGATEWAYID, areaStr, areaStr, datatype)
writedf[colName] = writedftmp.iloc[:, 3]

# total load, SCE 2,
areaStr = 'Area_total'
colName = 'root.%s.RK99900000003.`%s`.`LD3_total@%s`.%s'%(HEADGATEWAYID, areaStr, areaStr, datatype)
writedf[colName] = writedftmp.iloc[:, 4]

#----------extend data to 2024-12-30-------------------------
'''
# 创建一个新日期范围，对应每日的复制
base_date = '2024-10-18'
end_date = '2024-12-30'
date_range = pd.date_range(start=base_date, end=end_date, freq='D')

# 空的DataFrame，用于存储所有复制的数据
df_all_days = pd.DataFrame()

# 遍历每一天，复制数据，并更新时间戳
for single_date in date_range:
    # 复制df
    df_copy = writedf.copy()
    # 更新时间戳
    df_copy['Time'] = df_copy['Time'].str.replace(base_date, single_date.strftime('%Y-%m-%d'))

    # 将复制的数据合并到新的DataFrame
    df_all_days = pd.concat([df_all_days, df_copy], ignore_index=True)
'''

loadMeterDatafromDataFrame(writedf)
print ('-------------finished loading ini data---------------\n')
#loadMeterDatafromDataFrame(df_all_days)

# 将字典写入JSON文件
file_name = 'devidMapping.json'
with open(file_name, 'w') as json_file:
    json.dump(nameDict, json_file, indent=4)

#-----------------------write opt 1 data-----------------------------------------
csvfileOpt = 'OptPower.csv'
readdfopt = pd.read_csv(csvfileOpt)
headnamesopt = []
headnamesopt.append('Time')
column_names_opt = readdfopt.columns[1:].tolist()
for idx, ele in enumerate(column_names):
    strtmp = ele.split('_')
    if len(strtmp) >= 4:
        nametmp = ele.replace('Opt', 'Ini')
        devidName = nameDict[nametmp]
        newName = devidName.replace('P_ini', 'P_opt')
        headnamesopt.append(newName)
    else:
        devidName = nameDict[ele]
        newName = devidName.replace('_ini', '_opt')
        headnamesopt.append(newName)

writedfopt = readdfopt.copy()
writedfopt.columns = headnamesopt

#------------add the columns for the zone1 to zone3, and total  EV, AC, LD------------------------
#-----------zone 1--------------------------------------------------------------------------------
areaStr = 'Area_001'
datatype = 'P_opt1'
#devidStr = '%s_%d@%s' % (strtmp[1], startDevid, areaStr)

# zone 1 ac, column idx 1060
colName = 'root.%s.RK99900000003.`%s`.`AC_total@%s`.%s'%(HEADGATEWAYID, areaStr, areaStr, datatype)
writedfopt[colName] = writedfopt.iloc[:, 1:300].sum(axis=1)

# zone 1 ev, column idx 1061
colName = 'root.%s.RK99900000003.`%s`.`EV_total@%s`.%s'%(HEADGATEWAYID, areaStr, areaStr, datatype)
writedfopt[colName] = writedfopt.iloc[:, 301:350].sum(axis=1)

# zone 1 total load, mode 1, column idx 1062
colName = 'root.%s.RK99900000003.`%s`.`LD1_total@%s`.%s'%(HEADGATEWAYID, areaStr, areaStr, datatype)
writedfopt[colName] = writedfopt.iloc[:, 1:350].sum(axis=1) + writedfopt.iloc[:, 352] - writedfopt.iloc[:, 351]

# zone 1 total load, mode 2, column idx 1063
colName = 'root.%s.RK99900000003.`%s`.`LD2_total@%s`.%s'%(HEADGATEWAYID, areaStr, areaStr, datatype)
writedfopt[colName] = writedfopt.iloc[:, 1:350].sum(axis=1) + lddf.iloc[:, 1] - writedfopt.iloc[:, 351]

# zone 1 total load, mode 3, column idx 1064
colName = 'root.%s.RK99900000003.`%s`.`LD3_total@%s`.%s'%(HEADGATEWAYID, areaStr, areaStr, datatype)
writedfopt[colName] = writedfopt.iloc[:, 1:350].sum(axis=1) + lddf.iloc[:, 2] - writedfopt.iloc[:, 351]

#-----------zone 2--------------------------------------------------------------------------------
areaStr = 'Area_002'
datatype = 'P_opt1'
#devidStr = '%s_%d@%s' % (strtmp[1], startDevid, areaStr)
# zone 2 ac, column idx 1065
colName = 'root.%s.RK99900000003.`%s`.`AC_total@%s`.%s'%(HEADGATEWAYID, areaStr, areaStr, datatype)
writedfopt[colName] = writedfopt.iloc[:, 1+353:300+353].sum(axis=1)

# zone 2 ev, column idx 1066
colName = 'root.%s.RK99900000003.`%s`.`EV_total@%s`.%s'%(HEADGATEWAYID, areaStr, areaStr, datatype)
writedfopt[colName] = writedfopt.iloc[:, 301+353:350+353].sum(axis=1)

# zone 2 total load, mode 1, column idx 1067
colName = 'root.%s.RK99900000003.`%s`.`LD1_total@%s`.%s'%(HEADGATEWAYID, areaStr, areaStr, datatype)
writedfopt[colName] = writedfopt.iloc[:, 1+353:350+353].sum(axis=1) + writedfopt.iloc[:, 352+353] - writedfopt.iloc[:, 351+353]

# zone 2 total load, mode 2, column idx 1068
colName = 'root.%s.RK99900000003.`%s`.`LD2_total@%s`.%s'%(HEADGATEWAYID, areaStr, areaStr, datatype)
writedfopt[colName] = writedfopt.iloc[:, 1+353:350+353].sum(axis=1) + lddf.iloc[:, 1] - writedfopt.iloc[:, 351+353]

# zone 2 total load, mode 3, column idx 1069
colName = 'root.%s.RK99900000003.`%s`.`LD3_total@%s`.%s'%(HEADGATEWAYID, areaStr, areaStr, datatype)
writedfopt[colName] = writedfopt.iloc[:, 1+353:350+353].sum(axis=1) + lddf.iloc[:, 2] - writedfopt.iloc[:, 351+353]

#-----------zone 3--------------------------------------------------------------------------------
areaStr = 'Area_003'
datatype = 'P_opt1'
#devidStr = '%s_%d@%s' % (strtmp[1], startDevid, areaStr)
# zone 3 ac, column idx 1070
colName = 'root.%s.RK99900000003.`%s`.`AC_total@%s`.%s'%(HEADGATEWAYID, areaStr, areaStr, datatype)
writedfopt[colName] = writedfopt.iloc[:, 1+353*2:300+353*2].sum(axis=1)

# zone 3 ev, column idx 1071
colName = 'root.%s.RK99900000003.`%s`.`EV_total@%s`.%s'%(HEADGATEWAYID, areaStr, areaStr, datatype)
writedfopt[colName] = writedfopt.iloc[:, 301+353*2:350+353*2].sum(axis=1)

# zone 3 total load, mode 1, column idx 1072
colName = 'root.%s.RK99900000003.`%s`.`LD1_total@%s`.%s'%(HEADGATEWAYID, areaStr, areaStr, datatype)
writedfopt[colName] = writedfopt.iloc[:, 1+353*2:350+353*2].sum(axis=1) + writedfopt.iloc[:, 352+353*2] - writedfopt.iloc[:, 351+353*2]

# zone 3 total load, mode 2, column idx 1073
colName = 'root.%s.RK99900000003.`%s`.`LD2_total@%s`.%s'%(HEADGATEWAYID, areaStr, areaStr, datatype)
writedfopt[colName] = writedfopt.iloc[:, 1+353*2:350+353*2].sum(axis=1) + lddf.iloc[:, 1] - writedfopt.iloc[:, 351+353*2]

# zone 3 total load, mode 3, column idx 1074
colName = 'root.%s.RK99900000003.`%s`.`LD3_total@%s`.%s'%(HEADGATEWAYID, areaStr, areaStr, datatype)
writedfopt[colName] = writedfopt.iloc[:, 1+353*2:350+353*2].sum(axis=1) + lddf.iloc[:, 2] - writedfopt.iloc[:, 351+353*2]

#-----------total--------------------------------------------------------------------------------
areaStr = 'Area_total'
datatype = 'P_opt1'
#devidStr = '%s_%d@%s' % (strtmp[1], startDevid, areaStr)
# total ac, column idx 1075
colName = 'root.%s.RK99900000003.`%s`.`AC_total@%s`.%s'%(HEADGATEWAYID, areaStr, areaStr, datatype)
writedfopt[colName] = writedfopt.iloc[:, 1060] + writedfopt.iloc[:, 1060+5] + writedfopt.iloc[:, 1060+5*2]

# total ev, column idx 1076
colName = 'root.%s.RK99900000003.`%s`.`EV_total@%s`.%s'%(HEADGATEWAYID, areaStr, areaStr, datatype)
writedfopt[colName] = writedfopt.iloc[:, 1061] + writedfopt.iloc[:, 1061+5] + writedfopt.iloc[:, 1061+5*2]

# total load, mode 1, column idx 1077
colName = 'root.%s.RK99900000003.`%s`.`LD1_total@%s`.%s'%(HEADGATEWAYID, areaStr, areaStr, datatype)
writedfopt[colName] = writedfopt.iloc[:, 1062] + writedfopt.iloc[:, 1062+5] + writedfopt.iloc[:, 1062+5*2]

# total load, mode 2, column idx 1078
colName = 'root.%s.RK99900000003.`%s`.`LD2_total@%s`.%s'%(HEADGATEWAYID, areaStr, areaStr, datatype)
writedfopt[colName] = writedfopt.iloc[:, 1063] + writedfopt.iloc[:, 1063+5] + writedfopt.iloc[:, 1063+5*2]

# total load, mode 3, column idx 1078
colName = 'root.%s.RK99900000003.`%s`.`LD3_total@%s`.%s'%(HEADGATEWAYID, areaStr, areaStr, datatype)
writedfopt[colName] = writedfopt.iloc[:, 1064] + writedfopt.iloc[:, 1064+5] + writedfopt.iloc[:, 1064+5*2]


'''
#----------extend data to 2024-12-30-------------------------
# 创建一个新日期范围，对应每日的复制
base_date = '2024-10-18'
end_date = '2024-12-30'
date_range = pd.date_range(start=base_date, end=end_date, freq='D')

# 空的DataFrame，用于存储所有复制的数据
df_all_days_opt = pd.DataFrame()

# 遍历每一天，复制数据，并更新时间戳
for single_date in date_range:
    # 复制df
    df_copy = writedfopt.copy()
    # 更新时间戳
    df_copy['Time'] = df_copy['Time'].str.replace(base_date, single_date.strftime('%Y-%m-%d'))

    # 将复制的数据合并到新的DataFrame
    df_all_days_opt = pd.concat([df_all_days_opt, df_copy], ignore_index=True)

loadMeterDatafromDataFrame(df_all_days_opt)
'''
loadMeterDatafromDataFrame(writedfopt)
print ('-------------finished loading opt 1 data---------------\n')

#-----------------------write opt mode 2 data-----------------------------------------
csvfileOpt = 'OptPower_2.csv'
readdfopt = pd.read_csv(csvfileOpt)
headnamesopt = []
headnamesopt.append('Time')
column_names_opt = readdfopt.columns[1:].tolist()
for idx, ele in enumerate(column_names):
    strtmp = ele.split('_')
    if len(strtmp) >= 4:
        nametmp = ele.replace('Opt', 'Ini')
        devidName = nameDict[nametmp]
        newName = devidName.replace('P_ini', 'P_opt')
        headnamesopt.append(newName)
    else:
        devidName = nameDict[ele]
        newName = devidName.replace('_ini', '_opt')
        headnamesopt.append(newName)

writedfopt = readdfopt.copy()
writedfopt.columns = headnamesopt

#------------add the columns for the zone1 to zone3, and total  EV, AC, LD------------------------
#-----------zone 1--------------------------------------------------------------------------------
areaStr = 'Area_001'
datatype = 'P_opt2'
#devidStr = '%s_%d@%s' % (strtmp[1], startDevid, areaStr)

# zone 1 ac, column idx 1060
colName = 'root.%s.RK99900000003.`%s`.`AC_total@%s`.%s'%(HEADGATEWAYID, areaStr, areaStr, datatype)
writedfopt[colName] = writedfopt.iloc[:, 1:300].sum(axis=1)

# zone 1 ev, column idx 1061
colName = 'root.%s.RK99900000003.`%s`.`EV_total@%s`.%s'%(HEADGATEWAYID, areaStr, areaStr, datatype)
writedfopt[colName] = writedfopt.iloc[:, 301:350].sum(axis=1)

# zone 1 total load, mode 1, column idx 1062
colName = 'root.%s.RK99900000003.`%s`.`LD1_total@%s`.%s'%(HEADGATEWAYID, areaStr, areaStr, datatype)
writedfopt[colName] = writedfopt.iloc[:, 1:350].sum(axis=1) + writedfopt.iloc[:, 352] - writedfopt.iloc[:, 351]

# zone 1 total load, mode 2, column idx 1063
colName = 'root.%s.RK99900000003.`%s`.`LD2_total@%s`.%s'%(HEADGATEWAYID, areaStr, areaStr, datatype)
writedfopt[colName] = writedfopt.iloc[:, 1:350].sum(axis=1) + lddf.iloc[:, 1] - writedfopt.iloc[:, 351]

# zone 1 total load, mode 3, column idx 1064
colName = 'root.%s.RK99900000003.`%s`.`LD3_total@%s`.%s'%(HEADGATEWAYID, areaStr, areaStr, datatype)
writedfopt[colName] = writedfopt.iloc[:, 1:350].sum(axis=1) + lddf.iloc[:, 2] - writedfopt.iloc[:, 351]

#-----------zone 2--------------------------------------------------------------------------------
areaStr = 'Area_002'
datatype = 'P_opt2'
#devidStr = '%s_%d@%s' % (strtmp[1], startDevid, areaStr)
# zone 2 ac, column idx 1065
colName = 'root.%s.RK99900000003.`%s`.`AC_total@%s`.%s'%(HEADGATEWAYID, areaStr, areaStr, datatype)
writedfopt[colName] = writedfopt.iloc[:, 1+353:300+353].sum(axis=1)

# zone 2 ev, column idx 1066
colName = 'root.%s.RK99900000003.`%s`.`EV_total@%s`.%s'%(HEADGATEWAYID, areaStr, areaStr, datatype)
writedfopt[colName] = writedfopt.iloc[:, 301+353:350+353].sum(axis=1)

# zone 2 total load, mode 1, column idx 1067
colName = 'root.%s.RK99900000003.`%s`.`LD1_total@%s`.%s'%(HEADGATEWAYID, areaStr, areaStr, datatype)
writedfopt[colName] = writedfopt.iloc[:, 1+353:350+353].sum(axis=1) + writedfopt.iloc[:, 352+353] - writedfopt.iloc[:, 351+353]

# zone 2 total load, mode 2, column idx 1068
colName = 'root.%s.RK99900000003.`%s`.`LD2_total@%s`.%s'%(HEADGATEWAYID, areaStr, areaStr, datatype)
writedfopt[colName] = writedfopt.iloc[:, 1+353:350+353].sum(axis=1) + lddf.iloc[:, 1] - writedfopt.iloc[:, 351+353]

# zone 2 total load, mode 3, column idx 1069
colName = 'root.%s.RK99900000003.`%s`.`LD3_total@%s`.%s'%(HEADGATEWAYID, areaStr, areaStr, datatype)
writedfopt[colName] = writedfopt.iloc[:, 1+353:350+353].sum(axis=1) + lddf.iloc[:, 2] - writedfopt.iloc[:, 351+353]

#-----------zone 3--------------------------------------------------------------------------------
areaStr = 'Area_003'
datatype = 'P_opt2'
#devidStr = '%s_%d@%s' % (strtmp[1], startDevid, areaStr)
# zone 3 ac, column idx 1070
colName = 'root.%s.RK99900000003.`%s`.`AC_total@%s`.%s'%(HEADGATEWAYID, areaStr, areaStr, datatype)
writedfopt[colName] = writedfopt.iloc[:, 1+353*2:300+353*2].sum(axis=1)

# zone 3 ev, column idx 1071
colName = 'root.%s.RK99900000003.`%s`.`EV_total@%s`.%s'%(HEADGATEWAYID, areaStr, areaStr, datatype)
writedfopt[colName] = writedfopt.iloc[:, 301+353*2:350+353*2].sum(axis=1)

# zone 3 total load, mode 1, column idx 1072
colName = 'root.%s.RK99900000003.`%s`.`LD1_total@%s`.%s'%(HEADGATEWAYID, areaStr, areaStr, datatype)
writedfopt[colName] = writedfopt.iloc[:, 1+353*2:350+353*2].sum(axis=1) + writedfopt.iloc[:, 352+353*2] - writedfopt.iloc[:, 351+353*2]

# zone 3 total load, mode 2, column idx 1073
colName = 'root.%s.RK99900000003.`%s`.`LD2_total@%s`.%s'%(HEADGATEWAYID, areaStr, areaStr, datatype)
writedfopt[colName] = writedfopt.iloc[:, 1+353*2:350+353*2].sum(axis=1) + lddf.iloc[:, 1] - writedfopt.iloc[:, 351+353*2]

# zone 3 total load, mode 3, column idx 1074
colName = 'root.%s.RK99900000003.`%s`.`LD3_total@%s`.%s'%(HEADGATEWAYID, areaStr, areaStr, datatype)
writedfopt[colName] = writedfopt.iloc[:, 1+353*2:350+353*2].sum(axis=1) + lddf.iloc[:, 2] - writedfopt.iloc[:, 351+353*2]

#-----------total--------------------------------------------------------------------------------
areaStr = 'Area_total'
datatype = 'P_opt2'
#devidStr = '%s_%d@%s' % (strtmp[1], startDevid, areaStr)
# total ac, column idx 1075
colName = 'root.%s.RK99900000003.`%s`.`AC_total@%s`.%s'%(HEADGATEWAYID, areaStr, areaStr, datatype)
writedfopt[colName] = writedfopt.iloc[:, 1060] + writedfopt.iloc[:, 1060+5] + writedfopt.iloc[:, 1060+5*2]

# total ev, column idx 1076
colName = 'root.%s.RK99900000003.`%s`.`EV_total@%s`.%s'%(HEADGATEWAYID, areaStr, areaStr, datatype)
writedfopt[colName] = writedfopt.iloc[:, 1061] + writedfopt.iloc[:, 1061+5] + writedfopt.iloc[:, 1061+5*2]

# total load, mode 1, column idx 1077
colName = 'root.%s.RK99900000003.`%s`.`LD1_total@%s`.%s'%(HEADGATEWAYID, areaStr, areaStr, datatype)
writedfopt[colName] = writedfopt.iloc[:, 1062] + writedfopt.iloc[:, 1062+5] + writedfopt.iloc[:, 1062+5*2]

# total load, mode 2, column idx 1078
colName = 'root.%s.RK99900000003.`%s`.`LD2_total@%s`.%s'%(HEADGATEWAYID, areaStr, areaStr, datatype)
writedfopt[colName] = writedfopt.iloc[:, 1063] + writedfopt.iloc[:, 1063+5] + writedfopt.iloc[:, 1063+5*2]

# total load, mode 3, column idx 1078
colName = 'root.%s.RK99900000003.`%s`.`LD3_total@%s`.%s'%(HEADGATEWAYID, areaStr, areaStr, datatype)
writedfopt[colName] = writedfopt.iloc[:, 1064] + writedfopt.iloc[:, 1064+5] + writedfopt.iloc[:, 1064+5*2]

'''
#----------extend data to 2024-12-30-------------------------
# 创建一个新日期范围，对应每日的复制
base_date = '2024-10-18'
end_date = '2024-12-30'
date_range = pd.date_range(start=base_date, end=end_date, freq='D')

# 空的DataFrame，用于存储所有复制的数据
df_all_days_opt = pd.DataFrame()

# 遍历每一天，复制数据，并更新时间戳
for single_date in date_range:
    # 复制df
    df_copy = writedfopt.copy()
    # 更新时间戳
    df_copy['Time'] = df_copy['Time'].str.replace(base_date, single_date.strftime('%Y-%m-%d'))

    # 将复制的数据合并到新的DataFrame
    df_all_days_opt = pd.concat([df_all_days_opt, df_copy], ignore_index=True)

loadMeterDatafromDataFrame(df_all_days_opt)
'''
loadMeterDatafromDataFrame(writedfopt)
print ('-------------finished loading opt 2 data---------------\n')

#-----------------------write opt 3 data-----------------------------------------
csvfileOpt = 'OptPower_3.csv'
readdfopt = pd.read_csv(csvfileOpt)
headnamesopt = []
headnamesopt.append('Time')
column_names_opt = readdfopt.columns[1:].tolist()
for idx, ele in enumerate(column_names):
    strtmp = ele.split('_')
    if len(strtmp) >= 4:
        nametmp = ele.replace('Opt', 'Ini')
        devidName = nameDict[nametmp]
        newName = devidName.replace('P_ini', 'P_opt')
        headnamesopt.append(newName)
    else:
        devidName = nameDict[ele]
        newName = devidName.replace('_ini', '_opt')
        headnamesopt.append(newName)

writedfopt = readdfopt.copy()
writedfopt.columns = headnamesopt

#------------add the columns for the zone1 to zone3, and total  EV, AC, LD------------------------
#-----------zone 1--------------------------------------------------------------------------------
areaStr = 'Area_001'
datatype = 'P_opt3'
#devidStr = '%s_%d@%s' % (strtmp[1], startDevid, areaStr)

# zone 1 ac, column idx 1060
colName = 'root.%s.RK99900000003.`%s`.`AC_total@%s`.%s'%(HEADGATEWAYID, areaStr, areaStr, datatype)
writedfopt[colName] = writedfopt.iloc[:, 1:300].sum(axis=1)

# zone 1 ev, column idx 1061
colName = 'root.%s.RK99900000003.`%s`.`EV_total@%s`.%s'%(HEADGATEWAYID, areaStr, areaStr, datatype)
writedfopt[colName] = writedfopt.iloc[:, 301:350].sum(axis=1)

# zone 1 total load, mode 1, column idx 1062
colName = 'root.%s.RK99900000003.`%s`.`LD1_total@%s`.%s'%(HEADGATEWAYID, areaStr, areaStr, datatype)
writedfopt[colName] = writedfopt.iloc[:, 1:350].sum(axis=1) + writedfopt.iloc[:, 352] - writedfopt.iloc[:, 351]

# zone 1 total load, mode 2, column idx 1063
colName = 'root.%s.RK99900000003.`%s`.`LD2_total@%s`.%s'%(HEADGATEWAYID, areaStr, areaStr, datatype)
writedfopt[colName] = writedfopt.iloc[:, 1:350].sum(axis=1) + lddf.iloc[:, 1] - writedfopt.iloc[:, 351]

# zone 1 total load, mode 3, column idx 1064
colName = 'root.%s.RK99900000003.`%s`.`LD3_total@%s`.%s'%(HEADGATEWAYID, areaStr, areaStr, datatype)
writedfopt[colName] = writedfopt.iloc[:, 1:350].sum(axis=1) + lddf.iloc[:, 2] - writedfopt.iloc[:, 351]

#-----------zone 2--------------------------------------------------------------------------------
areaStr = 'Area_002'
datatype = 'P_opt3'
#devidStr = '%s_%d@%s' % (strtmp[1], startDevid, areaStr)
# zone 2 ac, column idx 1065
colName = 'root.%s.RK99900000003.`%s`.`AC_total@%s`.%s'%(HEADGATEWAYID, areaStr, areaStr, datatype)
writedfopt[colName] = writedfopt.iloc[:, 1+353:300+353].sum(axis=1)

# zone 2 ev, column idx 1066
colName = 'root.%s.RK99900000003.`%s`.`EV_total@%s`.%s'%(HEADGATEWAYID, areaStr, areaStr, datatype)
writedfopt[colName] = writedfopt.iloc[:, 301+353:350+353].sum(axis=1)

# zone 2 total load, mode 1, column idx 1067
colName = 'root.%s.RK99900000003.`%s`.`LD1_total@%s`.%s'%(HEADGATEWAYID, areaStr, areaStr, datatype)
writedfopt[colName] = writedfopt.iloc[:, 1+353:350+353].sum(axis=1) + writedfopt.iloc[:, 352+353] - writedfopt.iloc[:, 351+353]

# zone 2 total load, mode 2, column idx 1068
colName = 'root.%s.RK99900000003.`%s`.`LD2_total@%s`.%s'%(HEADGATEWAYID, areaStr, areaStr, datatype)
writedfopt[colName] = writedfopt.iloc[:, 1+353:350+353].sum(axis=1) + lddf.iloc[:, 1] - writedfopt.iloc[:, 351+353]

# zone 2 total load, mode 3, column idx 1069
colName = 'root.%s.RK99900000003.`%s`.`LD3_total@%s`.%s'%(HEADGATEWAYID, areaStr, areaStr, datatype)
writedfopt[colName] = writedfopt.iloc[:, 1+353:350+353].sum(axis=1) + lddf.iloc[:, 2] - writedfopt.iloc[:, 351+353]

#-----------zone 3--------------------------------------------------------------------------------
areaStr = 'Area_003'
datatype = 'P_opt3'
#devidStr = '%s_%d@%s' % (strtmp[1], startDevid, areaStr)
# zone 3 ac, column idx 1070
colName = 'root.%s.RK99900000003.`%s`.`AC_total@%s`.%s'%(HEADGATEWAYID, areaStr, areaStr, datatype)
writedfopt[colName] = writedfopt.iloc[:, 1+353*2:300+353*2].sum(axis=1)

# zone 3 ev, column idx 1071
colName = 'root.%s.RK99900000003.`%s`.`EV_total@%s`.%s'%(HEADGATEWAYID, areaStr, areaStr, datatype)
writedfopt[colName] = writedfopt.iloc[:, 301+353*2:350+353*2].sum(axis=1)

# zone 3 total load, mode 1, column idx 1072
colName = 'root.%s.RK99900000003.`%s`.`LD1_total@%s`.%s'%(HEADGATEWAYID, areaStr, areaStr, datatype)
writedfopt[colName] = writedfopt.iloc[:, 1+353*2:350+353*2].sum(axis=1) + writedfopt.iloc[:, 352+353*2] - writedfopt.iloc[:, 351+353*2]

# zone 3 total load, mode 2, column idx 1073
colName = 'root.%s.RK99900000003.`%s`.`LD2_total@%s`.%s'%(HEADGATEWAYID, areaStr, areaStr, datatype)
writedfopt[colName] = writedfopt.iloc[:, 1+353*2:350+353*2].sum(axis=1) + lddf.iloc[:, 1] - writedfopt.iloc[:, 351+353*2]

# zone 3 total load, mode 3, column idx 1074
colName = 'root.%s.RK99900000003.`%s`.`LD3_total@%s`.%s'%(HEADGATEWAYID, areaStr, areaStr, datatype)
writedfopt[colName] = writedfopt.iloc[:, 1+353*2:350+353*2].sum(axis=1) + lddf.iloc[:, 2] - writedfopt.iloc[:, 351+353*2]

#-----------total--------------------------------------------------------------------------------
areaStr = 'Area_total'
datatype = 'P_opt3'
#devidStr = '%s_%d@%s' % (strtmp[1], startDevid, areaStr)
# total ac, column idx 1075
colName = 'root.%s.RK99900000003.`%s`.`AC_total@%s`.%s'%(HEADGATEWAYID, areaStr, areaStr, datatype)
writedfopt[colName] = writedfopt.iloc[:, 1060] + writedfopt.iloc[:, 1060+5] + writedfopt.iloc[:, 1060+5*2]

# total ev, column idx 1076
colName = 'root.%s.RK99900000003.`%s`.`EV_total@%s`.%s'%(HEADGATEWAYID, areaStr, areaStr, datatype)
writedfopt[colName] = writedfopt.iloc[:, 1061] + writedfopt.iloc[:, 1061+5] + writedfopt.iloc[:, 1061+5*2]

# total load, mode 1, column idx 1077
colName = 'root.%s.RK99900000003.`%s`.`LD1_total@%s`.%s'%(HEADGATEWAYID, areaStr, areaStr, datatype)
writedfopt[colName] = writedfopt.iloc[:, 1062] + writedfopt.iloc[:, 1062+5] + writedfopt.iloc[:, 1062+5*2]

# total load, mode 2, column idx 1078
colName = 'root.%s.RK99900000003.`%s`.`LD2_total@%s`.%s'%(HEADGATEWAYID, areaStr, areaStr, datatype)
writedfopt[colName] = writedfopt.iloc[:, 1063] + writedfopt.iloc[:, 1063+5] + writedfopt.iloc[:, 1063+5*2]

# total load, mode 3, column idx 1078
colName = 'root.%s.RK99900000003.`%s`.`LD3_total@%s`.%s'%(HEADGATEWAYID, areaStr, areaStr, datatype)
writedfopt[colName] = writedfopt.iloc[:, 1064] + writedfopt.iloc[:, 1064+5] + writedfopt.iloc[:, 1064+5*2]

'''
#----------extend data to 2024-12-30-------------------------
# 创建一个新日期范围，对应每日的复制
base_date = '2024-10-18'
end_date = '2024-12-30'
date_range = pd.date_range(start=base_date, end=end_date, freq='D')

# 空的DataFrame，用于存储所有复制的数据
df_all_days_opt = pd.DataFrame()

# 遍历每一天，复制数据，并更新时间戳
for single_date in date_range:
    # 复制df
    df_copy = writedfopt.copy()
    # 更新时间戳
    df_copy['Time'] = df_copy['Time'].str.replace(base_date, single_date.strftime('%Y-%m-%d'))

    # 将复制的数据合并到新的DataFrame
    df_all_days_opt = pd.concat([df_all_days_opt, df_copy], ignore_index=True)

loadMeterDatafromDataFrame(df_all_days_opt)
'''
loadMeterDatafromDataFrame(writedfopt)
print ('-------------finished loading opt 3 data---------------\n')

'''
# 指定要读取的JSON文件名
file_name = 'devidMapping.json'

# 从JSON文件读取数据
with open(file_name, 'r') as json_file:
    dataDict = json.load(json_file)  # 将JSON内容解析为字典

'''

# close session connection.
session.close()

print("All iotdb executions done!!")
